package com.joe;

import freemarker.cache.FileTemplateLoader;
import freemarker.template.Configuration;
import freemarker.template.Template;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import static freemarker.template.Configuration.DEFAULT_INCOMPATIBLE_IMPROVEMENTS;

/**
 * 链接数据库 生成数据字典
 */
public class GenerateDataDictionary{

    private static final String TABLE_INFO_SQL = """
                SELECT
                	tables.TABLE_NAME ,
                	tab_columns.COLUMN_NAME,
                	tab_columns.DATA_TYPE,
                	tab_columns.DATA_LENGTH,
                	tab_columns.NULLABLE,
                	TAB_COLUMNS.DATA_DEFAULT,
                	tab_comments.COMMENTS TABLE_COMMENTS,
                	col_comments.COMMENTS COL_COMMENTS
                FROM
                	user_tables tables
                	LEFT JOIN user_tab_columns tab_columns ON TABLES.TABLE_NAME = tab_columns.TABLE_NAME
                	LEFT JOIN user_tab_comments tab_comments ON tables.TABLE_NAME = tab_comments.TABLE_NAME
                	LEFT JOIN user_col_comments col_comments ON tab_columns.TABLE_NAME = col_comments.TABLE_NAME\s
                				AND col_comments.COLUMN_NAME = tab_columns.COLUMN_NAME
                    ORDER BY TABLES.TABLE_NAME, tab_columns.COLUMN_ID
                """;

    public static void main(String[] args) throws Exception {
        // 数据库信息
        String url = "jdbc:oracle:thin:@10.82.42.236:1521/BBAPPDB";
        String username = "ams_sit";
        String password = "bgHocJo_6jdm";
        // 要输出的word文件位置
        String wordPath = "D:\\Desktop\\aa.doc";

        Connection connection = getConnection(url, username, password);

        // 获取模板文件的数据，按照自己的业务写
        Map<String,Object> dataMap = getTableInfoData(connection);

        // 创建FreeMarker配置类
        Configuration configuration = new Configuration(DEFAULT_INCOMPATIBLE_IMPROVEMENTS);
        configuration.setDefaultEncoding("utf-8");
        // 设置模板文件所在的文件夹位置 注意这里不是具体的模板文件 而是模板文件所在目录的位置
        String templateFolder = GenerateDataDictionary.class.getClassLoader().getResource("word-template").getPath();
        FileTemplateLoader fileTemplateLoader = new FileTemplateLoader(new File(templateFolder), true);
        configuration.setTemplateLoader(fileTemplateLoader);

        // 创建要输出的文件
        File file = new File(wordPath);

        // 找到对应的模板，即上面设置的模板文件目录下的模板文件全称
        Template template = configuration.getTemplate("数据字典.ftl");
        // 将数据替换到模板的指定位置,生成新的word文件
        OutputStreamWriter outputStreamWriter = new OutputStreamWriter(new FileOutputStream(file));
        template.process(dataMap, outputStreamWriter);

        outputStreamWriter.flush();
        outputStreamWriter.close();
    }

    private static Connection getConnection(String url, String userName, String password) throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        return DriverManager.getConnection(url, userName, password);
    }

    private static Map<String, Object> getTableInfoData(Connection connection) throws Exception{
        PreparedStatement preparedStatement = connection.prepareStatement(TABLE_INFO_SQL);
        ResultSet resultSet = preparedStatement.executeQuery();

        List<TableInfoEntity> tableInfoEntityList = new ArrayList<>();
        while (resultSet.next()){
            TableInfoEntity tableInfoEntity = new TableInfoEntity();
            String tableName = resultSet.getString("TABLE_NAME");
            String columnName = resultSet.getString("COLUMN_NAME");
            String dataType = resultSet.getString("DATA_TYPE");
            String dataLength = resultSet.getString("DATA_LENGTH");
            String nullable = resultSet.getString("NULLABLE");
            String dataDefault = resultSet.getString("DATA_DEFAULT");
            String tableComments = resultSet.getString("TABLE_COMMENTS");
            String colComments = resultSet.getString("COL_COMMENTS");


            tableInfoEntity.setTableName(GenerateDataDictionary.nullToEmpty(tableName))
                    .setColumnName(GenerateDataDictionary.nullToEmpty(columnName))
                    .setDataType(GenerateDataDictionary.nullToEmpty(dataType))
                    .setDataLength(GenerateDataDictionary.nullToEmpty(dataLength))
                    .setNullable(GenerateDataDictionary.nullToEmpty(nullable))
                    .setDataDefault(GenerateDataDictionary.nullToEmpty(dataDefault))
                    .setTableComments(GenerateDataDictionary.nullToEmpty(tableComments))
                    .setColComments(GenerateDataDictionary.nullToEmpty(colComments));
            tableInfoEntityList.add(tableInfoEntity);
        }

        // 按表分组
        Map<String, List<TableInfoEntity>> tableNameMap =
                tableInfoEntityList.stream().collect(Collectors.groupingBy(TableInfoEntity::getTableName));
        List<Map<String, Object>> tableMapList = new ArrayList<>();
        int i = 1;
        for (Map.Entry<String, List<TableInfoEntity>> tableNameMapEntry : tableNameMap.entrySet()) {
            String tableName = tableNameMapEntry.getKey();
            List<TableInfoEntity> tableInfoList = tableNameMapEntry.getValue();
            Map<String, Object> tableInfoMap = new HashMap<>();
            tableInfoMap.put("seqNo", i++);
            tableInfoMap.put("tableName", tableName);
            tableInfoMap.put("tableComments", tableInfoList.get(0).getTableComments());
            tableInfoMap.put("tableInfoList", tableInfoList);
            tableMapList.add(tableInfoMap);
        }

        Map<String, Object> dataMap = new HashMap<>();
        dataMap.put("tableMapList", tableMapList);
        return dataMap;
    }

    private static String nullToEmpty(String str){
        return str == null ? "" : str;
    }
}